package com.dfrz.dao;/*
 * @创建者：黄苟苟
 * @创建时间：2021/6/30   10:22
 * @Description描述
 **/

import com.dfrz.entry.CustomerInfo;
import com.dfrz.util.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author 86177
 */
public class CustomerDao {
    /*根据用户名登录*/
    public CustomerInfo login(String loginName, String passWord) {
        CustomerInfo customerinfo = null;
        String sqlStr = "select * from customerinfo where loginName=? and passWord=? ";
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        try {
            customerinfo = queryRunner.query(sqlStr,new BeanHandler <CustomerInfo>(CustomerInfo.class),loginName,passWord);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return customerinfo;
    }
    /**/
    public CustomerInfo queryUserByType(String val,Integer type){
        CustomerInfo customerinfo =null;
       String sqlStr="";
        if (type==1){
            sqlStr="select * from customerinfo where loginName=?";
        }else if (type==2){
            sqlStr="select *from customerinfo where phone=?";
        }else{
            sqlStr="select * from customerinfo where EMail=?";
        }
       QueryRunner queryRunner=new QueryRunner(JdbcUtils.getDataSource());
        try {
            customerinfo = queryRunner.query(sqlStr,new BeanHandler<CustomerInfo>(CustomerInfo.class),val);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return customerinfo;
    }
    /*添加用户*/
    public int insertUser(CustomerInfo customerinfo){
         int count = 0;
         String sqlStr="insert into customerinfo(name,loginName,passWord,gender,age,phone,EMail)" +
                 "values(?,?,?,?,?,?,?)";
         QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        try {
            count=queryRunner.update(sqlStr,customerinfo.getName(),customerinfo.getLoginName(),
                    customerinfo.getPassWord(),customerinfo.getGender(),customerinfo.getAge(),
                    customerinfo.getPhone(),customerinfo.getEMail());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return count;
    }
    /*更新用户*/
    public int updateUser(CustomerInfo customerinfo){
        int count = 0;
        String sqlStr="update customerinfo set name=?, loginName=?,passWord=?,gender=?,age=?,phone=?," +
                "EMail=? where id=?";
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        try {
            count=queryRunner.update(sqlStr,sqlStr,customerinfo.getName(),customerinfo.getLoginName(),
                    customerinfo.getPassWord(),customerinfo.getGender(),customerinfo.getAge(),
                    customerinfo.getPhone(),customerinfo.getEMail());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }
    /*逻辑删除用户*/
public int deleteUserById(Integer id){
    int count = 0;
    String sqlStr="update customerinfo  set status=1 where id=?";
    QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
    try {
        count=queryRunner.update(sqlStr,id);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    return count;
}
    public List<CustomerInfo> queryUserByPage(String name, int limit, int startNum) {
        List<CustomerInfo> listUser = null;
        String str = "";
        if (name!=null||!"".equals(name)) {
            str = "select * from customerinfo where name like concat('%',?,'%') and status=1 order by id desc limit ?,?";

            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
            try {
                listUser = queryRunner.query(str, new BeanListHandler<CustomerInfo>(CustomerInfo.class), name, startNum, limit);
            } catch (SQLException e) {
                e.printStackTrace();
            }

        } else {
            str = "select * from customerinfo where  status=1 order by id desc limit ?,?";
            QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
            try {
                listUser = queryRunner.query(str, new BeanListHandler<CustomerInfo>(CustomerInfo.class), startNum, limit);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return listUser;
    }


    /*测试环节*/
    public static void main(String[] args) {
        CustomerDao userinfoDao=new CustomerDao();
        CustomerInfo customerinfo = userinfoDao.login("黄苟苟","123456");
        System.out.println(customerinfo);

    }

}
